import numpy as np
import pymssql
import pandas
import tool
from numpy import linalg as la, mat

# 显示所有列
pandas.set_option('display.max_columns', None)
# 显示所有行
pandas.set_option('display.max_rows', None)


def getTagGroupList(cursor):
    cursor.execute("select * from Shop")
    shopList = tool.convert(cursor.fetchall())

    tagList1 = []
    allTagGroup = set()

    for shopItem in shopList:
        shopId = shopItem[0]
        cursor.execute("select * from ShopTag where shopId = " + shopId)
        tagList = tool.convert(cursor.fetchall())
        tagGroup = set()
        for tagItem in tagList:
            tagGroup.add(tagItem[1])
            allTagGroup.add(tagItem[1])
        tagList1.append(tagGroup)

    allTagGroupList = list(allTagGroup)
    return allTagGroupList


def getBackCateName(cursor):
    cursor.execute("SELECT column_name FROM information_schema.columns WHERE table_name = 'SearchScore'")

    result_list = list(cursor.fetchall())
    backCateNameList = []
    for item in result_list[1:len(result_list)]:
        backCateNameList.append(item[0])
    return backCateNameList


# 余弦相似度的计算
def cosSim(inA, inB):
    num = float(inA.T * inB)
    denom = la.norm(inA) * la.norm(inB)
    if denom == 0:
        return 0
    return 0.5 + 0.5 * (num / denom)


# 欧氏距离相似度的计算
def ecludSim(inA, inB):
    return 1.0 / (1.0 + la.norm(inA - inB))


def deleteZero(toBeTestedList, loopList):
    list1 = []
    list2 = []
    for i in range(len(toBeTestedList)):
        if (toBeTestedList[i] is not None) & (loopList[i] is not None):
            list1.append(float(toBeTestedList[i]))
            list2.append(float(loopList[i]))
    return list1, list2


def calculateSimilarPerson(toBeTestedList, data):
    rowSize = len(list(data.index.values))  # 行数

    toBeSortList = []
    for row in range(rowSize):
        loopList = []
        for item in data.iloc[row]:
            loopList.append(item)
        list1, list2 = deleteZero(toBeTestedList, loopList)
        cos = cosSim(mat(list1).T, mat(list2).T)
        result_list = [list(data.index.values)[row], cos]
        toBeSortList.append(result_list)
    toBeSortList.sort(key=lambda x: (-x[1]))
    return toBeSortList


def shopIdList_to_shopTagDF(shopIdList, allTagGroupList, cursor):
    all_result_list = []
    for shopId in shopIdList:
        sql1 = "select * from TagScore where shopId = '" + shopId + "'"
        cursor.execute(sql1)
        result_list = list(cursor.fetchall()[0])
        all_result_list.append(result_list[1:len(result_list)])
    shopListDF = pandas.DataFrame(all_result_list, index=shopIdList, columns=allTagGroupList)
    return shopListDF


# 最常光顾商店的属性
def most_frequent_stores_attribute(userId, shopIdList, allTagGroupList, cursor):
    sql = "select shopId, count(*) from Orders where userId = '" + userId + \
          "' and state = 4 group by shopId order by count(*) desc"
    cursor.execute(sql)
    most_frequent_shopId_tuple_list = tool.convert(cursor.fetchall())
    most_frequent_shopId_tuple_list_length = len(most_frequent_shopId_tuple_list)
    if most_frequent_shopId_tuple_list_length > 5:
        most_frequent_shopId_tuple_list_length = 5
    most_frequent_shopId_list = []
    for i in range(most_frequent_shopId_tuple_list_length):
        most_frequent_shopId_list.append(most_frequent_shopId_tuple_list[i][0])

    indexList = ["Num", "sumRate"]
    frequentDF = pandas.DataFrame(0.0, index=indexList, columns=allTagGroupList)

    for shopId in most_frequent_shopId_list:
        sql1 = "select * from TagScore where shopId = '" + shopId + "'"
        cursor.execute(sql1)
        result_list = list(cursor.fetchall()[0])
        result_list = result_list[1:len(result_list)]
        for index in range(len(result_list)):
            if result_list[index] is not None:
                frequentDF.iloc[0][allTagGroupList[index]] = frequentDF.iloc[0][allTagGroupList[index]] + 1
                frequentDF.iloc[1][allTagGroupList[index]] = \
                    float(frequentDF.iloc[1][allTagGroupList[index]]) + float(result_list[index])
    frequent_tag_score_result = []
    for col in frequentDF.columns.values:
        if frequentDF.iloc[0][col] != 0:
            frequent_tag_score_result.append(frequentDF.iloc[1][col] / frequentDF.iloc[0][col])
        else:
            frequent_tag_score_result.append(0)

    # all_result_list = []
    # for shopId in shopIdList:
    #     sql1 = "select * from TagScore where shopId = '" + shopId + "'"
    #     cursor.execute(sql1)
    #     result_list = list(cursor.fetchall()[0])
    #     all_result_list.append(result_list[1:len(result_list)])
    # shopListDF = pandas.DataFrame(all_result_list, index=shopIdList, columns=allTagGroupList)

    shopListDF = shopIdList_to_shopTagDF(shopIdList, allTagGroupList, cursor)

    for row in range(len(list(shopListDF.index.values))):
        for col in shopListDF.columns.values:
            if shopListDF.iloc[row][col] is None:
                shopListDF.iloc[row][col] = 0
    sorted_list = calculateSimilarPerson(frequent_tag_score_result, shopListDF)
    per_list = []
    for index in range(len(sorted_list)):
        if sorted_list[index][1] != 0:
            per_list.append([sorted_list[index][0], round(100 - (index / len(sorted_list) * 100), 4)])
        else:
            per_list.append([sorted_list[index][0], 0])

    return per_list


# 餐厅分类(例如烧烤，火锅等)属性
def backCateName_attribute(userId, shop_backCateNameList, cursor):
    sql = "select backCateName, count(*) from Orders inner join Shop on Shop.shopId=Orders.shopId and " \
          "Orders.userId = '" + userId + "' group by backCateName"
    cursor.execute(sql)
    backCateName_attribute_tuple_list = tool.convert(cursor.fetchall())
    backCateName_dict = dict()
    count = 0
    for item in backCateName_attribute_tuple_list:
        count = count + item[1]
    for item in backCateName_attribute_tuple_list:
        backCateName_dict[item[0]] = (float(item[1]) / count) * 100
    backCateName_attribute_list = []
    for item in shop_backCateNameList:
        if backCateName_dict.get(item) is not None:
            backCateName_attribute_list.append(backCateName_dict[item])
        else:
            backCateName_attribute_list.append(0)
    return backCateName_attribute_list


# 给予高分的商店属性:
def high_score_attribute(userId, shopDataFrame, allTagGroupList, cursor):
    sql = "select shopId, count(*) from Orders where userId = '" + userId + \
          "' and state = 4 and rate > 4 group by shopId order by count(*) desc"
    cursor.execute(sql)
    high_score_shop_tuple_list = tool.convert(cursor.fetchall())
    high_score_num = len(high_score_shop_tuple_list)
    if high_score_num > 5:
        high_score_num = 5
    high_score_shopId_list = []
    for index in range(high_score_num):
        high_score_shopId_list.append(high_score_shop_tuple_list[index][0])
    high_score_backCateName_list = []
    for shopId in high_score_shopId_list:
        sql1 = "select backCateName from Shop where shopId = '" + shopId + "'"
        cursor.execute(sql1)
        name = tool.convert(cursor.fetchall())
        high_score_backCateName_list.append(name[0][0])
    count = 0
    all_result_list = []
    for name in high_score_backCateName_list:
        beTestedShopId = high_score_shopId_list[count]
        sql1 = "select * from TagScore where shopId = '" + beTestedShopId + "'"
        cursor.execute(sql1)
        result_list = list(cursor.fetchall()[0])
        final_list = result_list[1:len(result_list)]

        df1 = shopDataFrame.loc[shopDataFrame[2] == name]
        shopIdList = list(df1[0])
        shopListDF = shopIdList_to_shopTagDF(shopIdList, allTagGroupList, cursor)
        for row in range(len(list(shopListDF.index.values))):
            for col in shopListDF.columns.values:
                if shopListDF.iloc[row][col] is None:
                    shopListDF.iloc[row][col] = 0
        sorted_list = calculateSimilarPerson(final_list, shopListDF)
        per_list = []
        for index in range(len(sorted_list)):
            # 赋予(0-80,0-90,0-100,0-110,0-120)的分数
            per_list.append([sorted_list[index][0],
                             round((100 - (index / len(sorted_list) * 100)) * (1.2 - 0.1 * count), 4)])
        all_result_list = all_result_list + per_list
        count += 1
    need_sorted_score_list = []  # 等待排序的最终成绩列表
    for shopId in list(shopDataFrame[0]):
        count = 0
        for item in all_result_list:
            if shopId == item[0]:
                count = count + item[1]
        need_sorted_score_list.append([shopId, count])
    need_sorted_score_list.sort(key=lambda i: i[1], reverse=True)
    per_list = []
    for index in range(len(need_sorted_score_list)):
        if need_sorted_score_list[index][1] != 0:
            per_list.append([need_sorted_score_list[index][0],
                             round(100 - (index / len(need_sorted_score_list) * 100), 4) * 2])
        else:
            per_list.append([need_sorted_score_list[index][0], 0])
    return per_list


def search_records_attribute(userId, backCateNameList, shopDataFrame, cursor):
    sql = "select * from SearchScore where userId = '" + userId + "'"
    cursor.execute(sql)
    result_list = list(cursor.fetchall()[0])
    user_searchScore_result = result_list[1:len(result_list)]
    count = 0
    user_searchScore_result_per_list = []
    for item in user_searchScore_result:
        count = count + item
    if count != 0:
        for item in user_searchScore_result:
            user_searchScore_result_per_list.append(round(float(item) / count, 4) * 50)
    else:
        for item in user_searchScore_result:
            user_searchScore_result_per_list.append(0.0)
    scoreDF = pandas.DataFrame([user_searchScore_result_per_list], index=["score"], columns=backCateNameList)
    return_list = []
    for index in shopDataFrame.index.values:
        return_list.append([shopDataFrame.loc[index][0], scoreDF.iloc[0][shopDataFrame.loc[index][2]]])
    return return_list


class recommendShop:
    def __init__(self, searchCondition):
        self.searchCondition = searchCondition

    def main(self):
        connect = pymssql.connect('127.0.0.1:1433/SQLEXPRESS', 'sa', '123456', '点餐系统')
        cursor = connect.cursor()

        allTagGroupList = getTagGroupList(cursor)
        backCateNameList = getBackCateName(cursor)

        sql1 = "select * from Shop where shopId in(select shopId from Shop where " \
               "(ACOS(COS(RADIANS(" + str(self.searchCondition.get('latitude')) + ")) * COS(RADIANS(latitude)) " \
                                                                                  "*COS(RADIANS(longitude) - RADIANS(" + str(
            self.searchCondition.get('longitude')) + ")) " \
                                                     "+SIN(RADIANS(" + str(
            self.searchCondition.get('latitude')) + ")) *SIN(RADIANS(latitude))) * 6378) < 4)"

        cursor.execute(sql1)
        shopList = pandas.DataFrame(tool.convert(cursor.fetchall()))
        shopIdList = shopList.iloc[:, 0].to_list()

        rate_list = shopList.iloc[:, 11].to_list()

        numDF = pandas.DataFrame([i * 20 for i in rate_list], index=shopIdList, columns=["rate"])

        # 最常光顾商店的属性
        return_most_frequent_stores_attribute = most_frequent_stores_attribute(self.searchCondition.get('userId'),
                                                                               shopIdList, allTagGroupList, cursor)
        numDF.insert(1, "frequent_stores", 0.0)
        for item in return_most_frequent_stores_attribute:
            numDF.loc[item[0]]["frequent_stores"] = item[1]

        # 餐厅分类(例如烧烤，火锅等)属性
        backCateName_list = backCateName_attribute(self.searchCondition.get('userId'), list(shopList.iloc[:][2]),
                                                   cursor)
        numDF.insert(2, "backCateName_score", backCateName_list)

        # 给予高分的商店属性
        high_score = high_score_attribute(self.searchCondition.get('userId'), shopList, allTagGroupList, cursor)
        numDF.insert(3, "high_score", 0.0)
        for item in high_score:
            numDF.loc[item[0]]["high_score"] = item[1]

        # 搜索记录属性:
        search_records_score = search_records_attribute(
            self.searchCondition.get('userId'), backCateNameList, shopList, cursor)
        numDF.insert(4, "search_records_score", 0.0)
        for item in search_records_score:
            numDF.loc[item[0]]["search_records_score"] = item[1]

        perfect_score_list = [100.0, 100.0, 100.0, 200.0, 50.0]
        sorted_list = calculateSimilarPerson(perfect_score_list, numDF)

        return_id_list = []
        for item in sorted_list:
            return_id_list.append(item[0])

        return_list = []
        for item in return_id_list:
            df1 = shopList.loc[shopList[0] == item]
            item_list = np.array(df1.iloc[0])
            dit = {
                "shopId": item_list[0],
                "shopName": item_list[1],
                "backCateName": item_list[2],
                "avgPrice": item_list[3],
                "sales": item_list[4],
                "shopPhone": item_list[5],
                "shopAddress": item_list[6],
                "frontImgUrl": item_list[7],
                "longitude": item_list[9],
                "latitude": item_list[10],
                "rate": item_list[11],
            }
            return_list.append(dit)
        return return_list
